<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/7/31 0031
 * Time: 下午 1:32
 */

namespace app\admin\controller;

use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Controller;
use think\Db;
use \think\Validate;
define('DS', DIRECTORY_SEPARATOR);
defined('APP_PATH') or define('APP_PATH', dirname($_SERVER['SCRIPT_FILENAME']) . DS);
defined('ROOT_PATH') or define('ROOT_PATH', dirname(realpath(APP_PATH)) . DS);

header('Access-Control-Allow-Origin:*');
header('Access-Control-Allow-Methods:POST,GET');
header('Access-Control-Allow-Headers:x-requested-with,content-type');
/**
 * 受益表
 * Class Income
 * @package app\admin\controller
 */
class Income extends Authinfo
{
    //受益列表
    public function income_index(){

        $map=array();
        $uid=input('uid');
        //自定义超级管理员id为10
        if($uid!=10){
            $company_id=Db::name('Member')->where(['id'=>$uid])->value('company_id');
            $company_name=Db::name('Department')->where(['id'=>$company_id])->value('name');
            $map[]=array('company','=',$company_name);
        }else{
            $company_name=input('company');
            if($company_name){
                $map[]=array('company','=',$company_name);
            }
        }

        //模糊查询 项目名称
        $cat_name=input('cat_name');
        if($cat_name){
            $map[]=array('cat_name','like','%'.$cat_name.'%');
        }

        //接收传过来的单位id得到单位名称
//        $company_name=input('company');
//        if($company_name){
//            $map[]=array('company','=',$company_name);
//        }
        //干部身份证号码
        $sfid=input('sfid');
        if($sfid){
            $map[]=array('sfid','=',$sfid);
        }
        //资金批次
        $batch=input('batch');
        if($batch){
            $map[]=array('batch','like','%'.$batch.'%');
        }
        // 分页
        $current=input('current');
        if(!$current){
            $current=1;
        }
        $pagenums=input('pagenums');
        if(!$pagenums){
            $pagenums=50;
        }
        $nowpagenums=($current-1)*$pagenums;
        $order=input('order');
        if(!$order){
            $order='id DESC';
        }
        if($map){
            $total=Db::name('Income')
                ->where($map)
                ->count('id');

            $info=Db::name('Income')
                ->field('hb_income.id,hb_income.batch,hb_income.company,hb_income.release_time,hb_income.cat_name')
                ->where($map)
                ->order($order)
                ->limit($nowpagenums,$pagenums)
                ->select();
            //列表头部的资金和人数
            $amount_count=Db::name('Income')
//                ->distinct('batch')
                ->where($map)
                ->sum('amount');
            $people_nums=Db::name('Income')
                ->distinct('batch')
                ->where($map)
                ->count();

        }else{
            $total=Db::name('Income')
//                ->distinct('batch')
                ->count('id');
            $info=Db::name('Income')
                ->field('hb_income.batch,hb_income.company,hb_income.release_time,hb_income.cat_name')
                ->order($order)
                ->group('batch')
                ->limit($nowpagenums,$pagenums)
                ->select();
            //列表头部的资金和人数
            $amount_count=Db::name('Income')
                ->sum('amount');
            $people_nums=Db::name('Income')
                ->count();

        }
        foreach($info as $key=>$value){
            //计算收益总金额
            $info[$key]['amount_count']=Db::name('Income')
                ->where(['batch'=>$value['batch']])
                ->where(['cat_name'=>$value['cat_name']])
                ->sum('amount');
            //实施时间合适转换
            $info[$key]['release_time']=time_nianyueri($value['release_time']);
        }

        if($info){
            $datas['status']=1;
            $datas['info']=$info;
            $datas['total']=$total;
            $datas['company']=$company_name;
            $datas['cat_name']=$cat_name;
            $datas['amount_count']=$amount_count;
            $datas['people_nums']=$people_nums;
            $datas['current']=$current;
        }else{
            $datas['status']=0;
            $datas['info']='无数据';
            $datas['amount_count']=$amount_count;
            $datas['people_nums']=$people_nums;
        }
        return json($datas);

    }

    //收益表查看
    public function income_view(){
        $batch=input('batch');
        $map=array();
        //受益人名称
        $name=input('name');
        if($name){
            $map[]=array('name','like','%'.$name.'%');
        }
        //地址查询
        $address=input('address');
        if($address){
            $map[]=array('address','like','%'.$address.'%');
        }
        // 分页
        $current=input('current');
        if(!$current){
            $current=1;
        }
        $pagenums=input('pagenums');
        if(!$pagenums){
            $pagenums=50;
        }
        $nowpagenums=($current-1)*$pagenums;
        $order=input('order');
        if(!$order){
            $order='hb_income.id DESC';
        }
        if($map){
            $total=Db::name('Income')
                ->where($map)
                ->where(['batch'=>$batch])
                ->count('hb_income.id');
            $info=Db::name('Income')
                ->where($map)
                ->where(['batch'=>$batch])
                ->order($order)
                ->limit($nowpagenums,$pagenums)
                ->select();
        }else{
            $total=Db::name('Income')
                ->where(['batch'=>$batch])
                ->count('hb_income.id');
            $info=Db::name('Income')
                ->where(['batch'=>$batch])
                ->order($order)
                ->limit($nowpagenums,$pagenums)
                ->select();
        }

        $people_num=Db::name('Income')
            ->where(['batch'=>$batch])
            ->where([])->count();
        if($info){
            foreach($info as $key=>$value){
                //实施时间合适转换
                $info[$key]['release_time']=time_nianyueri($value['release_time']);
            }
            //计算收益总金额 加入在一个项目批次中 点开查看会传id值过来
            $amount_count=Db::name('Income')
                ->where(['batch'=>$batch])
                ->sum('amount');
            $row=Db::name('Income')->field('cat_name,company')->where(['batch'=>$batch])->find();
            $datas['status']=1;
            $datas['info']=$info;
            $datas['total']=$total;
            $datas['amount_count']=$amount_count;
            $datas['company_name']=$row['company'];
            $datas['people_num']=$people_num;
            $datas['cat_name']=$row['cat_name'];
            $datas['current']=$current;
        }else{
            $datas['status']=0;
            $datas['info']='无数据！';
        }
//        dump($datas);exit;
        return json($datas);

    }

    //新增收益数据
    public function income_add(){
        $name=input('name');
        if($name){
            $data['name']=$name;
        }
        $sfid=input('sfid');
        if($sfid){
            $data['sfid']=$sfid;
        }
        $company=input('company');
        if($company){
            $data['company']=$company;
        }
        $num=input('num');
        if($num){
            $data['num']=$num;
        }
        $price=input('price');
        if($price){
            $data['price']=$price;
        }
        $unit=input('unit');
        if($unit){
            $data['unit']=$unit;
        }
        $batch=input('batch');
        if($batch){
            $data['batch']=$batch;
        }

        $cat_name=input('cat_name');
        if($cat_name){
            $data['cat_name']=$cat_name;
        } else {
            exit;
        }
        $amount=input('amount');
        if($amount){
            $data['amount']=$amount;
        }
        $cat_type=input('cat_type');
        if($cat_type){
            $data['cat_type']=$cat_type;
        }
        $release_time=input('release_time');
        if($release_time){
            $data['release_time']=strtotime($release_time);
        }
        $address=input('address');
        if($address){
            $data['address']=$address;
        }
        $data['create_time']=time();

        //判断是否已存在数据
        $info=Db::name('Income')
            ->where(['batch'=>$batch])
            ->where(['cat_name'=>$cat_name])
            ->find();
        if(!$info){
            if($data){
                $id=Db::name('Income')->insert($data);
                if($id){
                    $datas['status']=1;
                    $datas['info']='新增成功！';
                }else{
                    $datas['status']=0;
                    $datas['info']='无数据！';
                }
            }else{
                $datas['status']=0;
                $datas['info']='无数据！';
            }
        }else{
            $datas['status']=0;
            $datas['info']='数据已存在！';
        }

        return json($datas);
    }

    //编辑数据回显
    public function update_view(){
        $id=input('id');
        $list=Db::name('Income')->where(['id'=>$id])->find();
        $list['cat_list']=Db::name('Cat')->field('name')->where(['pid'=>$list['cat_type']])->select();
        if($list){
            $datas['status']=1;
            $datas['info']=$list;
        }else{
            $datas['status']=0;
            $datas['info']='无数据!';
        }
        return json($datas);
    }

    //修改数据
    public function income_update(){
        $id=input('id');
            if($id){
                $name=input('name');
                if($name){
                    $data['name']=$name;
                }
                $sfid=input('sfid');
                if($sfid){
                    $data['sfid']=$sfid;
                }
                $company=input('company');
                if($company){
                    $data['company']=$company;
                }
                $num=input('num');
                if($num){
                    $data['num']=$num;
                }
                $price=input('price');
                if($price){
                    $data['price']=$price;
                }
                $unit=input('unit');
                if($unit){
                    $data['unit']=$unit;
                }
                $batch=input('batch');
                if($batch){
                    $data['batch']=$batch;
                }

                $cat_name=input('cat_name');
                if($cat_name){
                    $data['cat_name']=$cat_name;
                } else {
                    exit;
                }
                $amount=input('amount');
                if($amount){
                    $data['amount']=$amount;
                }
                $cat_type=input('cat_type');
                if($cat_type){
                    $data['cat_type']=$cat_type;
                }
                $release_time=input('release_time');
                if($release_time){
                    $data['release_time']=strtotime($release_time);
                }
                $address=input('address');
                if($address){
                    $data['address']=$address;
                }
                if($data){
                    $result=Db::name('Income')->where(['id'=>$id])->update($data);
                    if($result){
                        $datas['status']=1;
                        $datas['info']='操作成功！';
                    }else{
                        $datas['status']=0;
                        $datas['info']='修改失败!';
                    }
                }else{
                    $datas['status']=0;
                    $datas['info']='无数据!';
                }
            }else{
                $datas['status']=0;
                $datas['info']='参数缺失!';
            }
        return json($datas);
    }
    //新增表中的项目分类
    public function cat_type(){
        $info=Db::name('Cat_type')->select();
        if($info){
            $datas['status']=1;
            $datas['info']=$info;
        }else{
            $datas['status']=0;
            $datas['info']='无数据';
        }
        return json($datas);
    }
    //新增表中的资金名称
    public function cat_list(){
        //接受的项目分类传的id
        $cat_name=input('cat_name');
        if($cat_name){
            //部门id
            $list=Db::name('Cat')->field('name,pid,department_id')->where(['pid'=>$cat_name])->select();
            if($list){
                $datas['status']=1;
                $datas['info']=$list;
            }else{
                $datas['status']=0;
                $datas['info']='无数据';
            }
        }else{
            $datas['status']=0;
            $datas['info']='无参数！';
        }
        return json($datas);
    }

    //新增表中部门分类列表
    public function index_department(){
        $name=input('name');
        $company=Db::name('Cat')->where(['name'=>$name])->value('department_id');
        $infos=Db::name('Department')->where('name',$company)->field('id,pid,name')->select();
        if($infos){
            $datas['status']=1;
            $datas['info']=$infos;
        }else{
            $datas['status']=0;
            $datas['info']='无数据！';
        }
        return json($datas);
    }

    //点开项目批次后的页面多个删除
    public function delete_view(){
        $idArray=input('id');
        if($idArray){
            $result = Db::name('Income')
                ->where('id', 'in', $idArray)
                ->delete();
            if($result){
                $datas['status'] = 1;
                $datas['info'] = '删除成功';
            }else{
                $datas['status'] = 0;
                $datas['info'] = '删除失败！';
            }
        }else{
            $datas['status']=0;
            $datas['info']='无参数！';
        }
        return json($datas);
    }
    
    //收益表删除
    public function income_delete(){
        $batchs=input('batch');
        if(is_array($batchs)) {
            $info = Db::name('Income')->where('batch','in',$batchs)->delete();
            if ($info) {
                $datas['status'] = 1;
                $datas['info'] = '删除成功';
            }else {
                $datas['status'] = 0;
                $datas['info'] = '删除失败！';
            }
        }else{
            $datas['status']=0;
            $datas['info']='无参数！';
        }
        return json($datas);
    }

    //php 多维数组去重
    function super_unique($array, $recursion = false){
        // 序列化数组元素,去除重复
        $result = array_map('unserialize', array_unique(array_map('serialize', $array)));
        // 递归调用
        if ($recursion) {
            foreach ($result as $key => $value) {
                if (is_array($value)) {
                    $result[ $key ] = super_unique($value);
                }
            }
        }
        return $result;
    }

    //列表左侧的项目数据列表
    public function cat_index(){
        $uid=input('uid');

        //通过用户的id得到此用户的部门
        $map=[];
        //自定义超级管理员id为10
        if($uid!=10){
            $company_id=Db::name('Member')->where(['id'=>$uid])->value('company_id');
            $map[]=array('id','=',$company_id);
        }
        $order = input('order');
        if($order){
            $order='id DESC';
        }// 排序
        if($map){
            $infos=Db::name('Department')
                ->field('hb_cat.name,hb_department.name')
                ->join('hb_cat','hb_department.name=hb_cat.department_id')
                ->where($map)
                ->order($order)
                ->select();
        }else{
            $infos=Db::name('Department')
                ->field('hb_cat.name,hb_department.name')
                ->join('hb_cat','hb_department.name=hb_cat.department_id')
                ->order($order)
                ->select();
        }
        foreach($infos as $k => $v){

            $infos[$k]['son']=Db::name('Cat')->field('department_id,name')->where(['department_id'=>$v['name']])->select();
        }
        if($infos){
            $datas['status']=1;
            $datas['info']=$this->super_unique($infos);
        }else{
            $datas['status']=0;
            $datas['info']='无数据！';
        }

        return json($datas);
    }



    //批量导入
    public function pladd(){
        $pathurl=input('pathurl');
//        $pathurl='uploads\wenjian\20180619\20180803.xlsx';
//        截取获得文档标题
//        $data=substr($pathurl,-13);
        $startRow=input('startRow');
        if(!$startRow){
            $startRow=2;
        }
        if($pathurl){
            $lujing=$pathurl;
            $excelFile=ROOT_PATH.$lujing;
            $excelReader =IOFactory::createReader('Xlsx');
            $excelReader->setReadDataOnly(true);
            $endRow = input('endRow');
            if (!$endRow) {
                $endRow = 2000;
            }
            $filterSubset=new Ireadefilter($startRow, $endRow,range('A', 'M'));
            $excelReader->setReadFilter($filterSubset);
            $phpexcel    = $excelReader->load($excelFile);
            $activeSheet = $phpexcel->getActiveSheet();
            $zongshu=$activeSheet->getHighestRow(); //总行数
            if($endRow>$zongshu){
                $endRow=$zongshu;
            }
            if($startRow<=$zongshu){
                $sheetData = $activeSheet->toArray(null, true, true, true);
                $highestColumn      = $activeSheet->getHighestColumn(); //最后列数所对应的字母，例如第2行就是B
                $data = array();
                for ($col = $startRow; $col <=$endRow; $col++) {
                    if($sheetData[$col]){
                        $data[] = $sheetData[$col];
                    }
                }
                $phpexcel->disconnectWorksheets();
                unset($phpexcel);
                $shujudata=array();
                if($data){
                    $nums=0;
                    foreach ($data as $key => $value){
                        $shujudata=array();
                        $shujudata['name']=$value['A'];
                        $shujudata['sex']=$value['B'];
                        $shujudata['mobile']=$value['C'];
                        $shujudata['sfid']=$this->NumToStr($value['D']);
                        $shujudata['company']=$value['E'];
                        $shujudata['cat_name']=$value['F'];
                        $shujudata['unit']=$value['G'];
                        $shujudata['price']=$value['H'];
                        $shujudata['num']=$value['I'];
                        $shujudata['amount']=$value['J'];
                        $shujudata['batch']=$value['K'];
                        $shujudata['release_time']=$value['M'];
                        $shujudata['address']=$value['L'];

                        if($value['D']){//干部sfid存在
                            //查询是否存在数据库中
                            $cz=Db::name('Income')
                                ->where('sfid',$value['D'])
                                ->where('cat_name',$value['F'])
                                ->where('batch',$value['K'])
                                ->find();
                            if(!$cz){
                                //不存在则进行添加操作
                                $shujudata['create_time']=time();
                                $idss=Db::name('Income')->insert($shujudata);
                                if($idss){
                                    $nums++;
                                    unset($shujudata);
                                }
                            }else{//存在则进行重复数据统一操作
                                    $shujudata['create_time']=time();
                                    $gx=Db::name('Income')->where('id',$cz['id'])->update($shujudata);
                                    $nums++;
                                    unset($shujudata);
                                }
                            }
                    }
                    $datas['status']=1;
                    $tiaoshu=$endRow;
                    $datas['cginfo']=$nums;
                    $datas['nextnums']=$endRow+1;
                    $datas['isend']=0;
                    unset($data);

                }else{
                    $datas['status']=0;
                    $datas['info']='无数据！';
                }
            }else{
                $datas['status']=1;
                $datas['info']='导入完毕';
                $datas['isend']=1;
            }
        }else{
            $datas['status']=0;
            $datas['info']='文件不存在';
        }
        return  json($datas);
    }
    //将科学计数法转换为原始数字字符串的方法
    function NumToStr($num){
        if (stripos($num,'e')===false) return $num;
        $num = trim(preg_replace('/[=\'"]/','',$num,1),'"');//出现科学计数法，还原成字符串
        $result = "";
        while ($num > 0){
            $v = $num - floor($num / 10)*10;
            $num = floor($num / 10);
            $result = $v . $result;
        }
        return $result;
    }
    function time_nianyueri($time = NULL,$format='Y-m-d'){
        $time = $time === NULL ? time() : intval($time);
        return date($format, $time);
    }
}